﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.IO.Ports;
using System.Threading;
using System.Text.RegularExpressions;

namespace SMSapplication
{
    public class LeaveApp
    {
        //string sender;
        //string message;
        //string 
        //FullDayLeave objFullDay = new FullDayLeave();
        //LeaveApplicationDA objLeaveApp = new LeaveApplicationDA();

        static readXML XMLreader = new readXML();
        static DataTable leaveTable = new DataTable();
        static DBconnection dbObj = new DBconnection();
        clsSMS objclsSMS = new clsSMS();
        SerialPort port = new SerialPort();
        ShortMessageCollection objShortMessageCollection = new ShortMessageCollection();

        public void leaveStart(string sender, string message, SerialPort port)
        {
            string[] msgData = message.Split('/');

            if (msgData[0] == "L" || msgData[0] == "l")
            {
                    analyseLeave(msgData);
                    sendMessage(port, sender, analyseLeave(msgData));
            }
            else
            {
                
            }
        }

        private string analyseLeave(string[] msgData)
        {
            string message = "";

            if (msgData.Length == 8)
            {
                string leaveType = msgData[1];
                string leaveCategory = msgData[2];
                string leaveFrom = msgData[3];
                string leaveTo = msgData[4];
                string leaveReason = msgData[5];
                string teacherID = msgData[6];
                string timeSlot = msgData[7];

                int count = validateAndCountDays(DateTime.Parse(leaveFrom), DateTime.Parse(leaveTo), teacherID);

                if (leaveType == "H" || leaveType == "h")
                {
                    leaveType = "HALF";
                    if (count > 1 || count == 0)
                        message = "Invalid leave request";
                    else
                        message = validateAvailable(msgData, count, leaveType);
                }
                else if (leaveType == "S" || leaveType == "s")
                {
                    leaveType = "SHORT";
                    if (count > 1 || count == 0)
                        message = "Invalid leave request";
                    else
                        message = validateAvailable(msgData, count, leaveType);
                }
                else if (leaveType == "F" || leaveType == "f")
                {
                    leaveType = "FULL";
                    if (count == 0)
                        message = "Invalid leave request";
                    else
                        message = validateAvailable(msgData, count, leaveType);
                }
                else
                {
                    message = "Invalid leave request";
                }
            }
            else
            {
                message = "Invalid message format";
            }

            
            return message;
        }


        // validate requested days
        private int validateAndCountDays(DateTime FromDate, DateTime ToDate, string teacherID)
        {
            string holiday = "", greaterThanToday = "", requestedEarlier = "";
            bool weekend;
            int countDays = 0;

            try
            {
                for (DateTime i = FromDate; i <= ToDate; i = i.AddDays(1))
                {
                    holiday = validateWhetherHoliDay(i);
                    weekend = validateWhetherWeekendDay(i);
                    greaterThanToday = validateRequstedDateGreaterThanToday(i);
                    requestedEarlier = validateLeaveRequestedEarlier(i, teacherID);

                    if (holiday == "" && weekend == true && greaterThanToday == "" && requestedEarlier == "")
                    {
                        countDays++;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return countDays;
        }


        // validate i th date is whether a holiday
        private string validateWhetherHoliDay(DateTime Date)
        {
            string message = "";
            string query = "";
            XMLreader = new readXML();

            try
            {
               // query = XMLreader.readXMLtxt("checkWhetherHoliday", "~/DATA/XML/LeaveXML.xml");
                query = "SELECT  HOLIDAY_NAME FROM CALENDAR_HOLIDAYS WHERE ('@REQUESTING_DATE' BETWEEN HOLIDAY_FROM AND HOLIDAY_TO)";
                query = query.Replace("@REQUESTING_DATE", Date.ToString("yyyy/M/d"));

                leaveTable = dbObj.Search(query);

                if (leaveTable.Rows.Count > 0)
                    message = leaveTable.Rows[0][0].ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return message;
        }


        // validate i th date is whether a weekend
        private bool validateWhetherWeekendDay(DateTime date)
        {
            if (date.DayOfWeek == DayOfWeek.Sunday || date.DayOfWeek == DayOfWeek.Saturday)
            {
                return false;
            }
            else
                return true;
        }


        // validate i th date is greater than today
        private string validateRequstedDateGreaterThanToday(DateTime Date)
        {
            string message = "";

            DateTime currentDate = new DateTime();
            currentDate = DateTime.Now;

            TimeSpan diffBetweenTwoDays = (Date - currentDate);

            if (Convert.ToInt32(diffBetweenTwoDays.TotalDays) < 0)
                message = "You have requesting already passed date !";

            return message;
        }


        // validate i th date is requested earlier
        private string validateLeaveRequestedEarlier(DateTime Date, string teacher_id)
        {
            string message = "";
            string query = "";
            XMLreader = new readXML();

            try
            {
               // query = XMLreader.readXMLtxt("checkLeaveRequestedEarlier", "~/DATA/XML/LeaveXML.xml");
                query = "SELECT LEAVE_ID FROM LEAVE_APPLICATION_FORM WHERE(DATE_FORMAT('@REQUESTING_DATE','%y/%m/%d') BETWEEN START_DATE AND END_DATE) AND TEACHER_ID='@TEACHER_ID'";
                query = query.Replace("@REQUESTING_DATE", Date.ToString("yyyy/M/d"));
                query = query.Replace("@TEACHER_ID", teacher_id);

                leaveTable = dbObj.Search(query);

                if (leaveTable.Rows.Count > 0)
                    message = "Date Requested Earlier";
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return message;
        }


        // validate available leaves and if ok update the database
        private string validateAvailable(string[] msgData, int count, string leaveType)
        {
            string message = "";
            DateTime currentDate = new DateTime();
            currentDate = DateTime.Now;
            string numOfLeaves;
            string leaveCategory;

            numOfLeaves = countAvailableAndPendingLeaves(msgData[6]);
            string[] amountOfLeaves = numOfLeaves.Split(new string[] { "," }, StringSplitOptions.None);

            string availableCasual = (Math.Truncate(Double.Parse(amountOfLeaves[0]))).ToString();
            string availableMedical = (Math.Truncate(Double.Parse(amountOfLeaves[1]))).ToString();
            string pedingCasual = (Math.Round(Double.Parse(amountOfLeaves[2]))).ToString();
            string pendingMedical = (Math.Round(Double.Parse(amountOfLeaves[3]))).ToString();
            string pendingDuty = (Math.Round(Double.Parse(amountOfLeaves[4]))).ToString();
            string pendingNopay = (Math.Round(Double.Parse(amountOfLeaves[5]))).ToString();

            if (msgData[2] == "M" || msgData[2] == "m")
            {
                leaveCategory = "MED";
                if (Int32.Parse(availableMedical) >= count)
                {
                    addLeaveToDB(msgData[6], msgData[3], msgData[4], count, msgData[5], leaveType, leaveCategory, msgData[7]);
                    message = "Leave Requesting is Successfull";
                }
            }
            else if (msgData[2] == "C" || msgData[2] == "c")
            {
                leaveCategory = "CAS";
                if (Int32.Parse(availableCasual) >= count)
                {
                    addLeaveToDB(msgData[6], msgData[3], msgData[4], count, msgData[5], leaveType, leaveCategory, msgData[7]);
                    message = "Leave Requesting is Successfull";
                }
            }
            else if (msgData[2] == "D" || msgData[2] == "d")
            {
                leaveCategory = "DUT";
                addLeaveToDB(msgData[6], msgData[3], msgData[4], count, msgData[5], leaveType, leaveCategory, msgData[7]);
                message = "Leave Requesting is Successfull";
            }
            else if (msgData[2] == "N" || msgData[2] == "n")
            {
                leaveCategory = "NOP";
                addLeaveToDB(msgData[6], msgData[3], msgData[4], count, msgData[5], leaveType, leaveCategory, msgData[7]);
                message = "Leave Requesting is Successfull";
            }
            else
                return message = "requesting is not successfull";

            return message;
        }

       
        // count leaves taken
        public string countAvailableAndPendingLeaves(string teacherID)
        {
            string numOfLeaves = "";
            DataTable dataTable = new DataTable();
            string query;
            XMLreader = new readXML();

            try
            {
               // query = XMLreader.readXMLtxt("countAvailablePendingLeaves", "~/DATA/XML/LeaveXML.xml");
                query = "SELECT( ";
		        query = query + "SELECT (SUM(DURATION))   FROM";
		        query = query + "LEAVE_APPLICATION_FORM WHERE TEACHER_ID = '@TEACHERID' AND LEAVE_TYPE_ID='CAS' AND STATUS=2 AND";
		        query = query + "(YEAR(END_DATE) = YEAR(CURDATE()) OR YEAR(START_DATE) = YEAR(CURDATE()))";
	            query = query + ") AS CASUALTAKEN,(";
		        query = query + "SELECT (SUM(DURATION))   FROM";
		        query = query + "LEAVE_APPLICATION_FORM WHERE TEACHER_ID = '@TEACHERID' AND LEAVE_TYPE_ID='MED' AND STATUS=2 AND";
		        query = query + "(YEAR(END_DATE) = YEAR(CURDATE()) OR YEAR(START_DATE) = YEAR(CURDATE()))";
	            query = query + ")AS MEDICALTAKEN,(";
		        query = query + "SELECT (SUM(DURATION))  FROM";
		        query = query + "LEAVE_APPLICATION_FORM WHERE TEACHER_ID = '@TEACHERID' AND LEAVE_TYPE_ID='CAS' AND STATUS=0 AND";
		        query = query + "(YEAR(END_DATE) = YEAR(CURDATE()) OR YEAR(START_DATE) = YEAR(CURDATE()))";
	            query = query + ") AS CASUALPENDING,(";
		        query = query + "SELECT (SUM(DURATION))   FROM";
		        query = query + "LEAVE_APPLICATION_FORM WHERE TEACHER_ID = '@TEACHERID' AND LEAVE_TYPE_ID='MED' AND STATUS=0 AND";
		        query = query + "(YEAR(END_DATE) = YEAR(CURDATE()) OR YEAR(START_DATE) = YEAR(CURDATE()))";
	            query = query + ") AS MEDICALPENDING,(";
		        query = query + "SELECT (SUM(DURATION))   FROM";
		        query = query + "LEAVE_APPLICATION_FORM WHERE TEACHER_ID = '@TEACHERID' AND LEAVE_TYPE_ID='DUT' AND STATUS=0 AND";
		        query = query + "(YEAR(END_DATE) = YEAR(CURDATE()) OR YEAR(START_DATE) = YEAR(CURDATE()))";
                query = query + ") AS DUTYPENDING,(";
		        query = query + "SELECT (SUM(DURATION))   FROM";
		        query = query + "LEAVE_APPLICATION_FORM WHERE TEACHER_ID = '@TEACHERID' AND LEAVE_TYPE_ID='NOP' AND STATUS=0 AND";
		        query = query + "(YEAR(END_DATE) = YEAR(CURDATE()) OR YEAR(START_DATE) = YEAR(CURDATE()))";
	            query = query + ") AS NOPAYPENDING";

                query = query.Replace("@TEACHERID", teacherID);
                dataTable = dbObj.Search(query);

                for (int i = 0; i < 6; i++)
                {
                    if (dataTable.Rows[0][i].ToString() == "null" || dataTable.Rows[0][i].ToString() == "")
                    {
                        if (i == 0)
                            numOfLeaves = maxLeaves("maxCas").ToString();
                        else if (i == 1)
                            numOfLeaves = numOfLeaves + "," + maxLeaves("maxMedi").ToString();
                        else
                            numOfLeaves = numOfLeaves + "," + "0";
                    }
                    else
                    {
                        if (i == 0)
                            numOfLeaves = (maxLeaves("maxCas") - Double.Parse(dataTable.Rows[0][i].ToString())).ToString();
                        else if (i == 1)
                            numOfLeaves = numOfLeaves + "," + (maxLeaves("maxMedi") - Double.Parse(dataTable.Rows[0][i].ToString())).ToString();
                        else
                            numOfLeaves = numOfLeaves + "," + dataTable.Rows[0][i].ToString();
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return numOfLeaves;
        }



        // should be in leave app list
        public double maxLeaves(string leave_type)
        {
            DataTable dataTable = new DataTable();
            XMLreader = new readXML();
            string query="";

            try
            {
                if (leave_type == "maxMedi")
                {
                    //query = XMLreader.readXMLtxt(leave_type, "~/DATA/XML/LeaveXML.xml");
                    query = "SELECT MAX_DAYS  FROM LEAVE_TYPE WHERE LEAVE_TYPE_ID = 'MED' ";
                }
                else if (leave_type == "maxCas")
                {
                    query = "SELECT MAX_DAYS  FROM LEAVE_TYPE WHERE LEAVE_TYPE_ID = 'CAS'  ";
                }

                dbObj = new DBconnection();
                dataTable = dbObj.Search(query);
                if (dataTable.Rows[0]["MAX_DAYS"].ToString() == "null" || dataTable.Rows.Count == 0 || dataTable.Rows[0]["MAX_DAYS"].ToString() == "")
                {
                    return 0;
                }
                else
                    return double.Parse(dataTable.Rows[0]["MAX_DAYS"].ToString());

            }
            catch (Exception ex)
            {
                throw ex;
            }

        }


        public void addLeaveToDB(string teacherID, string from, string to, int count, string reason, string leaveType, string leaveCategory, string timePeriod)
        {
            string query = "";
            string leaveID;
            XMLreader = new readXML();

            try
            {
                //query = XMLreader.readXMLtxt("addLeave", "~/DATA/XML/LeaveXML.xml");
                query = "INSERT INTO LEAVE_APPLICATION_FORM";
			    query = query + "(TEACHER_ID, LEAVE_ID, START_DATE, END_DATE, DURATION, REASON, STATUS, LEAVE_TYPE_ID, LEAVE_CATEGORY_ID, ABSENT_TIME_PERIOD)";
			    query = query + "VALUES ('@TEACHER_ID', '@LEAVE_ID', DATE_FORMAT('@START_DATE','%y/%m/%d'), DATE_FORMAT('@END_DATE','%y/%m/%d'), ";
                query = query + "CAST('@DURATION' AS DECIMAL(10,6)),  '@REASON', CAST('@STATUS' AS SIGNED INTEGER) , '@LEAVE_TYPE_ID', '@LEAVE_CATEGORY_ID', '@ABSENT_TIME_PERIOD') ";
                
                leaveID = getLeaveId(teacherID).ToString();

                query = query.Replace("@TEACHER_ID",  teacherID);
                query = query.Replace("@LEAVE_ID",  leaveID.ToString());
                query = query.Replace("@START_DATE", DateTime.Parse(from).ToString("yyyy/M/d"));
                query = query.Replace("@END_DATE", DateTime.Parse(to).ToString("yyyy/M/d"));
                query = query.Replace("@DURATION", count.ToString());
                query = query.Replace("@REASON",  reason);
                query = query.Replace("@STATUS",  "0");
                query = query.Replace("@LEAVE_TYPE_ID", leaveType);
                query = query.Replace("@LEAVE_CATEGORY_ID", leaveCategory);
                query = query.Replace("@ABSENT_TIME_PERIOD", timePeriod);

                dbObj.Modify(query);

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int getLeaveId(string teacherId)
        {
            string query = "";
            try
            {
              //  query = XMLreader.readXMLtxt("getLeaveId", "~/DATA/XML/LeaveXML.xml");

                query = "SELECT MAX(CAST(LEAVE_ID AS SIGNED)) AS LEAVE_ID FROM LEAVE_APPLICATION_FORM WHERE TEACHER_ID='@TEACHER_ID' ";
                query = query.Replace("@TEACHER_ID", teacherId);
                leaveTable = dbObj.Search(query);
                if (leaveTable.Rows[0]["LEAVE_ID"].ToString() == "null" || leaveTable.Rows[0]["LEAVE_ID"].ToString() == "")
                {
                    return 1;
                }
                else
                {
                    return (Int32.Parse(leaveTable.Rows[0]["LEAVE_ID"].ToString()) + 1);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        private void sendMessage(SerialPort port, string receiver, string textMessage)
        {
            try
            {

                if (objclsSMS.sendMsg(port, receiver, textMessage))
                {
                    //MessageBox.Show("Message has sent successfully");
                    //lblerrorg.Text = "Message has sent successfully";
                }
                else
                {
                    //MessageBox.Show("Failed to send message");
                    //lblerrorg.Text = "Failed to send message";
                }

            }
            catch (Exception ex)
            {
                //lblerrorg.Text = ex.Message;
            }
        }
    }
}
